<?php

namespace app\common\service;

use app\common\enums\SubjectQuestionStatusEnum;
use app\common\model\banxi\core\CoreVocabularyLexicon;
use app\common\model\banxi\course\CourseQuestion as CourseQuestionModel;
use app\common\model\banxi\course\Lexicon;
use app\common\model\banxi\say\SayGrade;
use app\common\model\banxi\say\SayTeachSentence;
use app\common\model\banxi\subject\Subject;
use app\common\model\banxi\subject\SubjectGrade;
use app\common\model\banxi\subject\SubjectKnowledge;
use app\common\model\banxi\subject\SubjectQuestion;
use app\common\service\queue\QueueService;
use ba\Exception;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;
use think\db\exception\PDOException;
use think\exception\ErrorException;
use think\exception\ValidateException;
use think\facade\Db;

class ImportService extends ApiService
{

    private function checkFile($file)
    {
        $fileName = $file->getOriginalName();  // 原始文件名
        $extension = pathinfo($fileName, PATHINFO_EXTENSION);
        if (!in_array($extension, ['xlsx', 'xls'])) {
            throw new ValidateException('请上传xlsx或xls文件');
        }
        return true;
    }

    //全科问题导入
    public function questionImport($file)
    {
        try {
            $this->checkFile($file);
            // 加载Excel文件
            $spreadsheet = IOFactory::load($file);

            // 获取第一个工作表
            $worksheet = $spreadsheet->getActiveSheet();

            // 获取所有图片
            $images = $worksheet->getDrawingCollection();

            // 创建一个数组来存储图片位置信息
            $imageCoordinates = [];
            foreach ($images as $image) {
                if ($image instanceof Drawing) {
                    $imageCoordinates[] = [
                        'path' => $image->getPath(),
                        'cell' => $image->getCoordinates(),
                        'row' => $image->getCoordinates()[1], // 获取行号
                        'col' => $image->getCoordinates()[0]   // 获取列号
                    ];
                }
            }
            // 获取最高行和列
            $highestRow = $worksheet->getHighestRow();
            $highestColumn = $worksheet->getHighestColumn();
            $highestColumnIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn);
            $UploadService = new UploadService();
            // 从第二行开始处理
            $errorList = [];
            $savaData = [];
            $optionValues = [
                3 => "A",
                4 => "B",
                5 => "C",
                6 => "D"
            ];
            $questionType = [
                '单选题' => 1,
                '多选题' => 2,
                '解答题' => 3,
            ];
            $fieldValue = [
                1 => 'name',
                2 => 'titlePicture',
                11 => 'difficulty'
            ];
            $subjectList = [];
            $subjectGradeList = [];
            $subjectKnowledgeList = [];
            $questionList = [];
            for ($row = 2; $row <= $highestRow; $row++) {
                $item = [];
                $flag = true;
                $error = '';
                for ($col = 1; $col <= $highestColumnIndex; $col++) {
                    $cellCoordinate = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($col) . $row;
                    // 检查当前单元格是否有图片
                    $filePath = '';
                    $isImage = false;
                    foreach ($imageCoordinates as $img) {
                        if ($img['cell'] == $cellCoordinate) {
                            $filePath = $img['path'];
                            $isImage = true;
                            break;
                        }
                    }

                    if ($isImage) {
                        if (isset($questionList[$filePath])) {
                            $content = $questionList[$filePath];
                        } else {
                            $savePath = '/question/' . date('Ymd');
                            $content = $UploadService->uploadFile($filePath, $filePath, $savePath);
                            if (!$content) {
                                $flag = false;
                                $error = '图片上传失败';
                                break;
                            }
                            $questionList[$filePath] = $content;
                        }
                    } else {
                        $content = $worksheet->getCell($cellCoordinate)->getValue();
                    }

                    if (in_array($col, [3, 4, 5, 6])) {
                        $value = $optionValues[$col];
                        $item['option'][] = [
                            'value' => $value,
                            'name' => $content,
                            'type' => $isImage ? 2 : 1
                        ];
                    } else if ($col == 7) {
                        if (!isset($questionType[$content])) {
                            $flag = false;
                            $error = '不符合要求的题型';
                            break;
                        }
                        $item['questionType'] = $questionType[$content];
                    } else if ($col == 8) {
                        $answer = $content ? explode(',', $content) : [];
                        $item['answer'] = array_map(function ($v) {
                            return strtoupper($v);
                        }, $answer);
                    } else if ($col == 9) {
                        $item['analysis'] = $isImage ? "<img src='{$content}' alt=''>" : $content;
                    } else if ($col == 10) {
                        if (isset($subjectKnowledgeList[$content])) {
                            $subjectKnowledgeId = $subjectKnowledgeList[$content];
                        } else {
                            $knowledge = explode('-', $content);
                            $knowledgeName = $knowledge[0];
                            if (count($knowledge) >= 2) {
                                $knowledgeName = $knowledge[1];
                            }
                            $subjectKnowledgeId = SubjectKnowledge::where(['name' => $knowledgeName])->value('id');
                            if (!$subjectKnowledgeId) {
                                $flag = false;
                                $error = '知识点不存在';
                                break;
                            }
                            $subjectKnowledgeList[$content] = $subjectKnowledgeId;
                        }
                        $item['subjectKnowledgeId'] = $subjectKnowledgeId;
                    } else if ($col == 12) {
                        if (isset($subjectGradeList[$content])) {
                            $subjectGradeId = $subjectGradeList[$content];
                        } else {
                            $subjectGradeId = SubjectGrade::where(['name' => $content])->value('id');
                            if (!$subjectGradeId) {
                                $flag = false;
                                $error = '年级不存在';
                                break;
                            }
                            $subjectGradeList[$content] = $subjectGradeId;
                        }
                        $item['subjectGradeId'] = $subjectGradeId;
                    } else if ($col == 13) {
                        if (isset($subjectList[$content])) {
                            $subjectId = $subjectList[$content];
                        } else {
                            $subjectId = Subject::where(['name' => $content])->value('id');
                            if (!$subjectId) {
                                $flag = false;
                                $error = '科目不存在';
                                break;
                            }
                            $subjectList[$content] = $subjectId;
                        }
                        $item['subjectId'] = $subjectId;
                    } else {
                        $field = $fieldValue[$col];
                        $item[$field] = $content ?: '';
                    }
                }
                if (!$flag) {
                    $errorList[] = [
                        'row' => "第{$row}行",
                        'errMsg' => $error
                    ];
                    continue;
                }
                $answerCount = count($item['answer']);
                if (in_array($item['questionType'], [1, 2])) {
                    if (!$answerCount) {
                        $errorList[] = [
                            'row' => "第{$row}行",
                            'errMsg' => '没有设置答案选项'
                        ];
                        continue;
                    }
                    $optionAnswer = array_column($item['option'], 'value');
                    $inOptions = true;
                    for ($i = 0; $i < $answerCount; $i++) {
                        if (!in_array($item['answer'][$i], $optionAnswer)) {
                            $inOptions = false;
                            break;
                        }
                    }
                    if (!$inOptions) {
                        $errorList[] = [
                            'row' => "第{$row}行",
                            'errMsg' => '答案不存在选项中'
                        ];
                        continue;
                    }

                }
                if ($item['questionType'] == 1 && $answerCount > 1) {
                    $errorList[] = [
                        'row' => "第{$row}行",
                        'errMsg' => '答案选项设置错误'
                    ];
                    continue;
                }
                if (in_array($item['questionType'], [1, 2])) {
                    $item['option'] = json_encode($item['option']);
                    $item['answer'] = json_encode($item['answer']);
                } else {
                    $item['option'] = '';
                    $item['answer'] = '';
                }
                $item['sortNum'] = 1;
                $item['status'] = SubjectQuestionStatusEnum::E1;
                $savaData[] = $item;
            }
            if ($savaData) {
                $subjectQuestion = new SubjectQuestion();
                $subjectQuestion->saveAll($savaData);
            }
        } catch (Exception|ValidateException|PDOException $e) {
            $this->setError($e->getMessage());
            return false;
        }
        return $errorList;
    }

    //课程词库导入
    public function courseWordImport($file, $curseId)
    {
        try {
            $this->checkFile($file);
            // 加载Excel文件
            $spreadsheet = IOFactory::load($file);
            // 获取第一个工作表
            $worksheet = $spreadsheet->getActiveSheet();

            $highestRow = $worksheet->getHighestRow();
            $fieldValue = [];
            for ($row = 2; $row <= $highestRow; $row++) {
                $cellCoordinate = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex(1) . $row;
                $fieldValue[] = $worksheet->getCell($cellCoordinate)->getValue();
            }
            $lexiconModel = new Lexicon();
            $lexiconModel->importExcel($curseId, $fieldValue);
        } catch (Exception|ValidateException|PDOException $e) {
            $this->setError($e->getMessage());
            return false;
        }
        return true;
    }

    public function courseQuestionImport($file, $courseId, $dataId, $dataType)
    {
        try {
            $this->checkFile($file);
            // 加载Excel文件
            $spreadsheet = IOFactory::load($file);
            // 获取第一个工作表
            $worksheet = $spreadsheet->getActiveSheet();

            $highestRow = $worksheet->getHighestRow();
            $highestColumn = $worksheet->getHighestColumn();
            $highestColumnIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn);
            $importExcelList = [];
            $errorList = [];
            $optionList = [
                '3' => 'A',
                '4' => 'B',
                '5' => 'C',
                '6' => 'D',
                '7' => 'E',
                '8' => 'F',
                '9' => 'G',
            ];
            $questionType = [
                '单选' => '1',
                '多选' => '2',
            ];
            $field = [
                '1' => 'name',
                '11' => 'analysis',
                '12' => 'sortNum',
            ];
            for ($row = 2; $row <= $highestRow; $row++) {
                $temp = [
                    'courseId' => $courseId,
                    'dataId' => $dataId,
                    'dataType' => $dataType
                ];
                $option = [];
                $answer = [];
                for ($col = 1; $col <= $highestColumnIndex; $col++) {
                    $cellCoordinate = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($col) . $row;
                    $cellValue = $worksheet->getCell($cellCoordinate)->getValue();
                    if ($col == 2) {
                        $temp['questionType'] = $questionType[$cellValue];
                    } elseif (in_array($col, [3, 4, 5, 6, 7, 8, 9])) {
                        if ($cellValue) {
                            $option[] = [
                                'name' => $cellValue,
                                'value' => $optionList[$col]
                            ];
                        }
                    } else if ($col == 10) {
                        $answer = explode(',', $cellValue);
                    } else {
                        if ($field[$col] == 'analysis') {
                            $cellValue = $cellValue ?: '';
                        }
                        $temp[$field[$col]] = $cellValue;
                    }
                }
                $temp['option'] = json_encode($option);
                $temp['answer'] = json_encode($answer);
                $importExcelList[] = $temp;
            }
            $courseQuestionModel = new CourseQuestionModel();
            $courseQuestionModel->importExcel($importExcelList);
        } catch (Exception|ValidateException|PDOException $e) {
            $this->setError($e->getMessage());
            return false;
        }
        return $errorList;
    }

    public function vocabularyLexiconImport($file, $settingId)
    {
        try {
            $this->checkFile($file);
            // 加载Excel文件
            $spreadsheet = IOFactory::load($file);
            // 获取第一个工作表
            $worksheet = $spreadsheet->getActiveSheet();

            $highestRow = $worksheet->getHighestRow();
            $highestColumn = $worksheet->getHighestColumn();
            $highestColumnIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn);
            $importExcelList = [];
            $errorList = [];
            $optionList = [
                '2' => 'A',
                '3' => 'B',
                '4' => 'C',
                '5' => 'D',
                '6' => 'E',
                '7' => 'F',
                '8' => 'G',
            ];
            $field = [
                '1' => 'word',
            ];
            for ($row = 2; $row <= $highestRow; $row++) {
                $temp = [
                    'settingId' => $settingId
                ];
                $option = [];
                $answer = [];
                for ($col = 1; $col <= $highestColumnIndex; $col++) {
                    $cellCoordinate = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($col) . $row;
                    $cellValue = $worksheet->getCell($cellCoordinate)->getValue();
                    if (in_array($col, [2, 3, 4, 5, 6, 7, 8])) {
                        if ($cellValue) {
                            $option[] = [
                                'index' => bcsub($col, 2, 0),
                                'name' => $cellValue,
                                'value' => $optionList[$col],
                                'check' => ''
                            ];
                        }
                    } else if ($col == 9) {
                        $answer = explode(',', $cellValue);
                    } else {
                        $temp[$field[$col]] = $cellValue;
                    }
                }
                $temp['option'] = json_encode($option);
                $temp['answer'] = json_encode($answer);
                $importExcelList[] = $temp;
            }
            $coreVocabularyLexiconModel = new CoreVocabularyLexicon();
            $coreVocabularyLexiconModel->importExcel($importExcelList, $settingId);
        } catch (Exception|ValidateException|PDOException $e) {
            $this->setError($e->getMessage());
            return false;
        }
        return $errorList;
    }

    public function saySentenceImport($file)
    {
        Db::startTrans();
        $line = 0;
        $fieldKey = '';
        try {
            $this->checkFile($file);
            // 加载Excel文件
            $spreadsheet = IOFactory::load($file);
            // 获取第一个工作表
            $worksheet = $spreadsheet->getActiveSheet();
            $images = $worksheet->getDrawingCollection();
            $imageCoordinates = [];

            foreach ($images as $image) {
                if ($image instanceof Drawing) {
                    $cell = $image->getCoordinates();
                    $imageCoordinates[$cell] = $image->getPath();
                }
            }


            $highestRow = $worksheet->getHighestRow();
            $highestColumn = $worksheet->getHighestColumn();
            // 获取列索引
            $highestColumnIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn);
            $field = [];
            for ($col = 1; $col <= $highestColumnIndex; $col++) {
                $cellCoordinate = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($col) . 1;
                $cellValue = $worksheet->getCell($cellCoordinate)->getValue();
                $field[$col] = $cellValue;
            }
            $uploadService = new UploadService();
            $domain = $uploadService->getDomain();
            $gradeInfo = [];
            $color = [
                '红色' => '#FF0000',
                '绿色' => '#02AB4F',
                '蓝色' => '#0070C0',
                '橘色' => '#F88825',
            ];
            $parentId = [];
            $coreVocabularyJobList = [];
            for ($row = 2; $row <= $highestRow; $row++) {
                $temp = [];
                $line = $row;
                for ($col = 1; $col <= $highestColumnIndex; $col++) {
                    $cellCoordinate = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($col) . $row;
                    $cellValue = $worksheet->getCell($cellCoordinate)->getFormattedValue();
                    $fieldKey = $field[$col];
                    //如果字段有标题但单元格为空，则根据字段类型决定是否跳过
                    if ($field[$col] && $cellValue == '') {
                        $isSpecialField = (mb_substr($fieldKey, 0, 4) === '语调意群' || $fieldKey === '句子-图片');
                        if (!$isSpecialField) {
                            continue;
                        }
                    }
                    if ($field[$col] == '句型出现所在年级') {
                        if (!isset($gradeInfo[$cellValue])) {
                            $grade = SayGrade::where(['name' => $cellValue])->field('id,sayCategoryId')->find();
                            if (empty($grade)) {
                                throw new ValidateException("第{$row}行年级不存在 {$field[$col]}-$cellValue");
                            }
                            $gradeInfo[$cellValue] = $grade;
                        }
                        $temp['gradeId'] = $gradeInfo[$cellValue]['id'];
                    } else if ($field[$col] == '本组句型-中文') {
                        $temp['sentenceGroupZh'] = $cellValue;
                    } else if ($field[$col] == '本组句型-英文') {
                        $temp['sentenceGroupEn'] = $cellValue;
                    } else if ($field[$col] == '句子功能') {
                        $temp['name'] = $cellValue;
                    } else if ($field[$col] == '所属母句') {
                        $temp['parentId'] = 0;
                        if ($cellValue != 0) {
                            $temp['parentId'] = $parentId[$cellValue - 1];
                        }
                    } else if ($field[$col] == '句子-英文') {
                        $temp['sentenceEn'] = $cellValue;
                        $sentenceEn = explode('-', $cellValue);
                        foreach ($sentenceEn as $key => $item) {
                            $temp['splitSentenceList'][$key]['valueEn'] = trim($item);
                        }
                    } else if ($field[$col] == '句子-中文') {
                        $temp['sentenceZh'] = $cellValue;
                        $sentenceZh = explode('—', $cellValue);
                        foreach ($sentenceZh as $key => $item) {
                            $temp['splitSentenceList'][$key]['valueZh'] = trim($item);
                            $temp['splitSentenceList'][$key]['audio'] = '';
                        }
                    } else if ($field[$col] == '句子-音标') {
                        $temp['sentencePhonetic'] = str_replace('|', '/', $cellValue);;
                        $sentencePhonetic = explode('-', $cellValue);
                        foreach ($sentencePhonetic as $key => $item) {
                            $temp['splitSentenceList'][$key]['phonetic'] = str_replace('|', '/', $item);;
                        }
                    } else if (in_array($field[$col], ['红色', '绿色', '蓝色', '橘色'])) {
                        if ($cellValue) {
                            list($wordEn, $wordZh) = preg_split('/[\{\}]/', $cellValue, -1, PREG_SPLIT_NO_EMPTY);
                            $temp['sentenceWordColorList'][] = [
                                'wordEn' => $wordEn,
                                'wordZh' => $wordZh,
                                'color' => $color[$field[$col]]
                            ];
                        }

                    } else if ($field[$col] && mb_substr($field[$col], 0, 4) == '语调意群') {
                        $filePath = $imageCoordinates[$cellCoordinate] ?? '';
                        if ($filePath) {
                            if (isset($questionList[$filePath])) {
                                $imgUrl = $questionList[$filePath];
                            } else {
                                $savePath = '/sentence/' . date('Ymd');
                                $imgUrl = $uploadService->uploadFile($filePath, $filePath, $savePath);
                                if (!$imgUrl) {
                                    throw new ValidateException("第{$row}行{$field[$col]}图片上传失败");
                                }
                                $questionList[$filePath] = $imgUrl;
                            }
                            $temp['intonationGroupList'][] = [
                                'image' => $imgUrl,
                                'audio' => ''
                            ];
                        }

                    } else if ($field[$col] == '句子-音频') {
                        $audioPaths = preg_split('/,/', $cellValue, -1, PREG_SPLIT_NO_EMPTY);
                        $audioPath = array_map('trim', $audioPaths);
                        $temp['sentenceAudio'] = $domain . urlencode_chinese_only_enhanced($audioPath[0]);
                        foreach ($audioPath as $key => $item) {
                            if ($key == 0) {
                                continue;
                            }
                            $temp['intonationGroupList'][$key - 1]['audio'] = $domain . urlencode_chinese_only_enhanced($item);
                            $temp['splitSentenceList'][$key - 1]['audio'] = $domain . urlencode_chinese_only_enhanced($item);
                        }
                    } else if ($field[$col] == '句子-核心词汇') {
                        if (!$cellValue || $cellValue == '无') {
                            continue;
                        }
                        $coreVocabulary = explode('|', $cellValue);
                        foreach ($coreVocabulary as $key => $item) {
                            $parts = preg_split('/[\[\]\{\}]/', $item, -1, PREG_SPLIT_NO_EMPTY);
                            $temp['coreVocabularyList'][$key] = [
                                'wordEn' => $parts[0],
                                'phonetic' => "/$parts[1]/",
                                'wordZh' => $parts[2],
                                'audio' => ''
                            ];
                        }
                    } else if ($field[$col] == '核心词汇-音频') {
                        if (!$cellValue || $cellValue == '无') {
                            continue;
                        }
                        $audioPaths = preg_split('/,/', $cellValue, -1, PREG_SPLIT_NO_EMPTY);
                        $audioPath = array_map('trim', $audioPaths);
                        foreach ($audioPath as $key => $item) {
                            $temp['coreVocabularyList'][$key]['audio'] = $domain . urlencode_chinese_only_enhanced($item);
                        }
                    } else if ($field[$col] == '句子-图片') {
                        $filePath = $imageCoordinates[$cellCoordinate] ?? '';
                        if ($filePath) {
                            if (isset($questionList[$filePath])) {
                                $imgUrl = $questionList[$filePath];
                            } else {
                                $savePath = '/sentence/' . date('Ymd');
                                $imgUrl = $uploadService->uploadFile($filePath, $filePath, $savePath);
                                if (!$imgUrl) {
                                    throw new ValidateException("第{$row}行{$field[$col]}图片上传失败");
                                }
                                $questionList[$filePath] = $imgUrl;
                            }
                            $temp['sentenceImage'] = $imgUrl;
                        }
                    } else if ($field[$col] == '题目') {
                        $temp['question'] = $cellValue;
                    } else if ($field[$col] && mb_substr($field[$col], 0, 2) == '答案') {
                        $temp['answer'][] = $cellValue;
                    }
                }
                if (empty($temp)) {
                    continue;
                }
                if($temp['coreVocabularyList']){
                    $coreVocabularyJobList = array_merge($temp['coreVocabularyList'], $coreVocabularyJobList);
                }
                $temp['expandVocabulary'] = '';
                $temp['sortNum'] = 1;
                $temp['status'] = 1;
                $temp['splitSentence'] = isset($temp['splitSentenceList']) ? json_encode($temp['splitSentenceList'], JSON_UNESCAPED_UNICODE) : '';
                $temp['sentenceWordColor'] = isset($temp['sentenceWordColorList']) ? json_encode($temp['sentenceWordColorList'], JSON_UNESCAPED_UNICODE) : '';
                $temp['intonationGroup'] = isset($temp['intonationGroupList']) ? json_encode($temp['intonationGroupList'], JSON_UNESCAPED_UNICODE) : '';
                $temp['coreVocabulary'] = isset($temp['coreVocabularyList']) ? json_encode($temp['coreVocabularyList'], JSON_UNESCAPED_UNICODE) : '';
                $temp['answer'] = implode(',', $temp['answer']);
                $sayTeachSentence = new SayTeachSentence();
                $sayTeachSentence->save($temp);
                if ($temp['parentId'] == 0) {
                    $parentId[] = $sayTeachSentence->id;
                }
            }
            Db::commit();
        } catch (Exception|ValidateException|PDOException $e) {
            Db::rollback();
            getResponseException($e->getMessage(), [], 1, 'ERROR');
        } catch (ErrorException $e) {
            Db::rollback();
            getResponseException("请检查第{$line}行{$fieldKey}要求格式是否正确" . $e->getMessage(), [], 1, 'ERROR');
        }
        $queueService = new QueueService();
        $queueService->addSayWordSplitJob($coreVocabularyJobList);
        return true;
    }
}